# 导入python库包
import pandas as pd
import numpy as np

# 数据导入
house = pd.read_csv('houses.csv')
# 数据形式
print(house.head())
#查看house表数据情况
print(house.info())
# 查看分类变量种类情况
print(house[["行政区", "租房类型", "楼层", "是否有电梯", "社区ID"]].nunique())
#创建处理副本
house_copy = house.copy()

# metro表数据情况
metro = pd.read_csv('metro.csv')
print(metro.head())
# 查看数据整体情况
print(metro.info())

# 查看数据重复值
print("重复值:",house_copy.duplicated().sum())
# 剔除重复值
house_copy.drop_duplicates(inplace=True)

# 查看数据缺失值
missing=house_copy[["楼层","最高楼层数","是否有电梯"]].isnull().sum().reset_index().rename(columns={0:'missNum'})
missing

# 查看数据异常值
# 1.分类特征描述性统计
house_copy[["行政区","租房类型","楼层","是否有电梯","社区ID"]].astype(object).describe()

# 2.数字特征描述性统计
house_copy[["房屋面积/平方米","每平方米租金价格/（元/平方米）",
            "最高楼层数","租房价格/元"]].describe()

import matplotlib.pyplot as plt
import seaborn as sns
# 3.数值型数据箱型图
boxplotlist = ["房屋面积/平方米","租房价格/元","每平方米租金价格/（元/平方米）","最高楼层数"]
fig = plt.figure(dpi=150,figsize=(15,5))
plt.rcParams['font.sans-serif'] ='SimHei'
plt.subplots_adjust(left=None, bottom=None, right=None, top=None, wspace=0.5, hspace=0.5)

for i in range(1,len(boxplotlist)+1):
    plt.subplot(2,2,i)
    sns.boxplot(house_copy[boxplotlist[i-1]], orient='h')
    plt.title(boxplotlist[i-1])
plt.show()

# 异常值处理
# 1.最高楼层数类别异常值检测与处理
house_copy[house_copy["最高楼层数"]==0]["最高楼层数"].value_counts()
# 异常值剔除：最高楼层数为0
house_copy = house_copy.drop(house_copy[house_copy.最高楼层数 == 0].index)
# 异常值剔除：最高楼层数为空,
house_copy = house_copy[house_copy['最高楼层数'].notna()]
# 查看楼层数据情况,
house_copy['最高楼层数'].describe()


import warnings
warnings.filterwarnings('ignore')  # 忽略无关警报信息
# 2. 楼层类别异常处理
# 第一步：提取真实数字楼层
aa = house_copy[(house_copy["楼层"]!='H')&(house_copy['楼层']!='M') &(house_copy['楼层']!='L')]
aa['楼层'] = aa['楼层'].astype(float)
aa['最高楼层数'] = aa['最高楼层数'].astype(float)
aa['one_third'] = aa['最高楼层数']/3
aa['two_third'] = aa['最高楼层数']*2/3
# 第二步：以最高楼层数量的每1/3作为楼层位置划分依据
def change_value(row):
    if row['楼层'] <= row['one_third']:
        return 'L'
    elif row['楼层'] <= row['two_third']:
        return 'M'
    else:
        return 'H'
# 第三步：使用apply方法，将自定义函数应用于每一行的数据
aa['楼层'] = aa.apply(change_value, axis=1)
# 将修改后的数据替换原始数据中的数值层数,替换完毕
house_copy.loc[aa.index,'楼层'] = aa['楼层']
house_copy['楼层'].value_counts()

# 3. 住房面积、单位租金、最高楼层、租金异常值处理
# 删除面积小于5的异常数据
house_copy = house_copy.drop(house_copy[house_copy["房屋面积/平方米"]<5].index)
# 使用3sigma原则剔除租金过高、面积过大的豪华房屋数据
aa = house_copy.copy()
def OutlierDetection(df,col,ks_res):
    # 计算均值
    u = df[col].mean()
    # 计算标准差
    std = df[col].std()
    if ks_res==1:
        # 定义3σ法则识别异常值
        # 识别异常值
        error = df[np.abs(df[col] - u) > 3 * std]
        # 剔除异常值，保留正常的数据
        data_c = df[np.abs(df[col] - u) <= 3 * std]
        return error,data_c

# 异常值处理
error,data_c = OutlierDetection(aa,'租房价格/元',1)
error1,data_c1 = OutlierDetection(data_c,'房屋面积/平方米',1)
error2,data_c2 = OutlierDetection(data_c1,'每平方米租金价格/（元/平方米）',1)
error3,data_c3 = OutlierDetection(data_c2,'最高楼层数',1)
# 数据存储
house1 = data_c3


# 查看数据异常处理后箱型图分布
fig = plt.figure(dpi=150,figsize=(15,5))
plt.subplots_adjust(left=None, bottom=None, right=None, top=None, wspace=0.5, hspace=0.5)

for i in range(1,len(boxplotlist)+1):
    plt.subplot(2,2,i)
    sns.boxplot(house1[boxplotlist[i-1]], orient='h')
    plt.title(boxplotlist[i-1])
plt.show()

# 处理后数据情况
house1[["房屋面积/平方米","每平方米租金价格/（元/平方米）","最高楼层数","租房价格/元"]].describe()

# 数据缺失值处理
# 1、查看异常值处理后数据缺失值情况
missing=house1.isnull().sum().reset_index().rename(columns={0:'missNum'})
print(missing)

# 2、剔除缺失值
house1 = house1.dropna(axis=0, how='any',  subset=None, inplace=False)
house1.info()


# 数据变换----地理维度变换
# house表地理位置数据变换
house2 = house1.copy()
# 1.先将地理位置列拆分为两列
names = ['行政区', '租房类型', '房屋面积/平方米', '租房价格/元', '每平方米租金价格/（元/平方米）',
         '楼层', '最高楼层数', '是否有电梯', '社区ID', '地理位置', '纬度', '经度']
house2 = pd.concat([house2, house2['地理位置'].str.split(',', expand=True)], axis=1)
house2.columns = names
house2['纬度'] = house2['纬度'].apply(lambda x:x[0:3]+'d'+x[3:])
house2['经度'] = house2['经度'].apply(lambda x:x[0:4]+'d'+x[4:])
house2 = house2.drop('地理位置',axis = 1)


# 2. 将度分秒转换为度
import math
import re
def to_degrees(s):
    s1 = s
    arr = re.findall("\d+",s1)
    # 将度分秒转换为弧度
    angle = math.radians(int(arr[0])) + math.radians(int(arr[1])/ 60) + math.radians(int(arr[2])/ 3600)
    # 将弧度转换为度
    return math.degrees(angle)
house2['经度'] = house2['经度'].apply(lambda x:to_degrees(x))
house2['纬度'] = house2['纬度'].apply(lambda x:to_degrees(x))


# 3.将经纬度以列表格式存储
house2 = house2.reset_index().drop("index",axis = 1)
house2["经纬度"] = pd.DataFrame({"经纬度":[[house2["纬度"][i],house2["经度"][i]]
                                           for i in range(0,len(house2))]})
house2["经纬度"] = house2["经纬度"].apply(lambda x:tuple(x))
print(house2.head())

# metro表地理位置数据变换
# 1.先将地理位置列拆分为两列
names = ['线路代码', '车站代码', '地理位置','纬度','经度']
metro1 = pd.concat([metro, metro['Geometry'].str.split(',', expand=True)], axis=1)
metro1.columns = names
metro1["纬度"] = metro1["纬度"].apply(lambda x:x[0:3]+"d"+x[3:])
metro1["经度"] = metro1["经度"].apply(lambda x:x[0:4]+"d"+x[4:])
metro1  = metro1.drop("地理位置",axis = 1)
# 2.将度分秒转换为度
metro1["经度"]= metro1["经度"].apply(lambda x:to_degrees(x))
metro1["纬度"]= metro1["纬度"].apply(lambda x:to_degrees(x))


# 3.将经纬度以列表格式存储
metro1["经纬度"] = pd.DataFrame({"经纬度":[[metro1["纬度"][i],metro1["经度"][i]]
                                           for i in range(0,len(metro1))]})
# 转换为元组
metro1["经纬度"] = metro1["经纬度"].apply(lambda x:tuple(x))
metro1.head()


# 数据准备
# 1.计算房屋社区位置与地铁站最近距离
import math
import haversine
from haversine import haversine,Unit

# 租房经纬度列表
rental_locations = house2["经纬度"]

# 地铁站经纬度列表
subway_locations = metro1["经纬度"]

# 计算每个社区到最近地铁站的距离
nearest_distances = []
nearest_subways = []
for rental_pos in rental_locations:
    nearest_distance = float('inf')
    nearest_subway = None

    for subway_pos in subway_locations:
        distance = haversine(rental_pos, subway_pos,unit=Unit.KILOMETERS)
        if distance < nearest_distance:
            nearest_distance = distance
            nearest_subway = subway_pos
    nearest_distances.append(nearest_distance)
    nearest_subways.append(nearest_subway)

#输出详细信息
# for i in range(len(nearest_subways)):
#     print("租屋经纬度：", rental_locations[i])
#     print("最近地铁经纬度：", nearest_subways[i])
#     print("最近距离：", nearest_distances[i], "千米")
#     print("-----------------------")

# 2.合并distance距离以及对应的最近地铁站点位置
house2[["distance","地理位置"]] = pd.DataFrame({"distance":nearest_distances,
                                                "地理位置":nearest_subways})

# 3.拆分最近地铁的经纬度
house2["纬度"] = house2["地理位置"].apply(lambda x:x[0])
house2["经度"] = house2["地理位置"].apply(lambda x:x[1])
house2.head()

# 4.变量名称修改，方便后续可视化
def change_name2(row):
    if row['是否有电梯'] == 1.0:
        return '有电梯'
    else:
        return '无电梯'
# 使用apply方法，将自定义函数应用于每一行的数据
house2['是否有电梯'] = house2.apply(change_name2, axis=1)

# 5.依据最近地铁合并house2和metro1，获得最近的地铁线路和站点信息
house_new = pd.merge(house2,metro1,on=["纬度","经度"],how = "left")

# 6.删除冗余列,获得清洗后的数据
house_new = house_new.drop(["经纬度_x","经纬度_y","地理位置","车站名称","纬度","经度"],axis = 1)
house_new.head()
house_new.to_csv('../tmp/house_new.csv',encoding='gbk',index=False)








